/*
 * DataBases 445 Library DBMS
 */

package gui;

import java.awt.BorderLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.FocusEvent;
import java.awt.event.FocusListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;

import dbConnection.Database;

/**
 * Class to create the gui for the Inventory Search Page for the
 * Administrative view. This includes the necessary front end, 
 * and back end code.
 * 
 * https://code.google.com/hosting/settings?saved=1&ts=1369905600
 * 
 * @author Prentice, Arslan, Herndon
 *
 */
@SuppressWarnings("serial")
public class AdminInventorySearchPanel extends JPanel {

	/** The Database object for the class. */
	public Database DB;

	/**
	 * Initializes the Gui + Backend code for the InventorySearchResult 
	 * panel object.
	 * 
	 * @param dB2 A DataBase object
	 */
	public AdminInventorySearchPanel(Database dB2) {
		this.DB = dB2;

		setLayout(new BorderLayout());

		final JPanel fieldSearch = new JPanel();
		final JPanel resultsPanel = new JPanel();
		//final JScrollPane resultsScroll = new JScrollPane();
		final JPanel buttons = new JPanel();
		resultsPanel.setLayout(new BoxLayout(resultsPanel, BoxLayout.Y_AXIS));

		final JTextField itemID = new JTextField();
		final JTextField inventoryID = new JTextField();
		final JTextField title = new JTextField();
		final JTextField author = new JTextField();
		final JTextField isbn = new JTextField();
		final JTextField type = new JTextField();

		final JLabel itemIDLabel = new JLabel("itemID");
		final JLabel inventoryIDLabel = new JLabel("inventoryID");
		final JLabel titleLabel = new JLabel("Title");
		final JLabel authorLabel = new JLabel("Author");
		final JLabel isbnLabel = new JLabel("ISBN");
		final JLabel typeLabel = new JLabel("Type");

		final JButton search = new JButton("Search");
		final JButton addItem= new JButton("+Item");
		addItem.setToolTipText("Add new item to database. Does NOT add additional inventory");

		EnterListener searchListener = new EnterListener(search);
		itemID.addActionListener(searchListener);
		inventoryID.addActionListener(searchListener);
		title.addActionListener(searchListener);
		author.addActionListener(searchListener);
		isbn.addActionListener(searchListener);
		type.addActionListener(searchListener);
		
		search.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) { 
				resultsPanel.removeAll();  //This may cause problems //second note: only for JFrames it seems
				if(AdminInventorySearchPanel.this.DB.con != null) {
					Statement statement = null;
					ResultSet resultSet = null;
					try {
						statement = AdminInventorySearchPanel.this.DB.con.createStatement();
						resultSet = statement.executeQuery("SELECT DISTINCT inventory.ItemId,inventory.InventoryId,item.Title,item.Author," +
								"item.ISBN,item.Type FROM inventory NATURAL JOIN item;");

						String[] keywords = {itemID.getText(), inventoryID.getText(), title.getText(), author.getText(),
								isbn.getText(), type.getText()};
						//if resultset is received and is not empty,
						// iterate over resultset to get values
						boolean cont = true;
						String keyword;
						String result;
						while (resultSet.next()) {	
							
							for (int i = 0; i<2; i++) {
								if (!keywords[i].equals("") && !resultSet.getString(i+1).equals((keywords[i]))) {
									cont = false;
									break;
								}	
							}

							for (int i = 2; i < 6; i++) {
								keyword = keywords[i].toLowerCase();
								result = resultSet.getString(i+1).toLowerCase();
								
								if (keyword.equals("") || result.contains(keyword) ||
										result.equals(keyword)) {
									continue;
								} else {
									cont = false;
									break;
								}	
							} 
							if (cont) {
								//AdminItemSearchResult a = new AdminItemSearchResult(Integer.parseInt(resultSet.getString(2)), AdminInventorySearchPanel.this.DB);
								resultsPanel.add(new AdminItemSearchResult(Integer.parseInt(resultSet.getString(2)), AdminInventorySearchPanel.this.DB));	
								//System.out.print(a.getSummary());
							}

							cont = true;
						}
					} catch (SQLException eee) {
						// 
					}
				}
				
				resultsPanel.revalidate();
				resultsPanel.repaint();
			}
		});

		//adds new item information, not inventory
		addItem.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				if (!title.getText().equals("")) { //title must be provided.
					
					if(AdminInventorySearchPanel.this.DB.con != null) {
						Statement statement = null;	  
						try {
							statement = AdminInventorySearchPanel.this.DB.con.createStatement();				

							StringBuilder sb = new StringBuilder();
							sb.append("INSERT INTO item (ItemID,Title,Type,Author,ISBN)VALUES (");
							sb.append((int)(Math.random()*10000));
							sb.append(",'");
							sb.append(title.getText());
							sb.append("','");
							sb.append(type.getText());
							sb.append("','");
							sb.append(author.getText());
							sb.append("','");
							sb.append(isbn.getText());
							sb.append("')");
							
							System.out.println(sb);
							statement.executeUpdate(sb.toString());
						} catch (SQLException ee) {
							//JOptionPane.showMessageDialog(this., ee.toString());
						}	
					}
					//clear fields
					itemID.setText("");
					inventoryID.setText("");
					title.setText("");
					author.setText("");
					isbn.setText("");
					type.setText("");
				}
			}
		});

		fieldSearch.setLayout(new GridLayout(2,0));

		fieldSearch.add(itemID);
		fieldSearch.add(inventoryID);
		fieldSearch.add(title);
		fieldSearch.add(author);
		fieldSearch.add(isbn);
		fieldSearch.add(type);
		fieldSearch.add(search);

		fieldSearch.add(itemIDLabel);
		fieldSearch.add(inventoryIDLabel);
		fieldSearch.add(titleLabel);
		fieldSearch.add(authorLabel);
		fieldSearch.add(isbnLabel);
		fieldSearch.add(typeLabel);
		fieldSearch.add(addItem);

		final JTextField addInventory = new JTextField("Enter Inventory ID...");
		final JButton addInventoryButton = new JButton("+Inventory");
		final JButton viewOverdue = new JButton("Overdue Items");
		
		addInventory.addFocusListener(new FocusListener() {

		    @Override
		    public void focusGained(FocusEvent focusEvent) {
			if (addInventory.getText().equals("Enter Inventory ID...")) {
			    addInventory.setText("");
			}
		    }

		    @Override
		    public void focusLost(FocusEvent focusEvent) {
			if(addInventory.getText().equals("")) {
			    addInventory.setText("Enter Inventory ID...");
			}
		    }
		    
		});

		//addInventory.setMinimumSize(new Dimension(100,100));
		addInventoryButton.setToolTipText("Add inventory-item ID must exist in database");
		addInventoryButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				//int proposedID = Integer.parseInt(addInventory.getText());
				if(AdminInventorySearchPanel.this.DB.con != null) {
					Statement statement = null;	  
					try {
						statement = AdminInventorySearchPanel.this.DB.con.createStatement();
						ResultSet result = statement.executeQuery("SELECT * FROM item WHERE " +
								"ItemId=" + Integer.parseInt(addInventory.getText()));
						if (result != null && result.next()) {
							statement = AdminInventorySearchPanel.this.DB.con.createStatement();
							StringBuilder sb = new StringBuilder();
							sb.append("INSERT INTO inventory (InventoryId, ItemId, Cond, ConditionLastChecked, CheckoutStatus) VALUES (");
							sb.append((int)(Math.random()*10000));
							sb.append(", ");
							sb.append(Integer.parseInt(addInventory.getText()));
							sb.append(",");
							sb.append("'Good'");
							sb.append(",");
							sb.append("curdate()");
							sb.append(",");
							sb.append("'Checked In')");
//							System.out.println(sb);
							statement.executeUpdate(sb.toString());						
						} else {
							// SHOW WARNING THAT ITEM IS NOT IN THE ITEM LIST
						}

					} catch (SQLException ee) {
						//JOptionPane.showMessageDialog(this., ee.toString());
					}	
				}
				//clear fields
				itemID.setText("");
				inventoryID.setText("");
				title.setText("");
				author.setText("");
				isbn.setText("");
				type.setText("");		

				addInventory.setText("");
			}
		});

		viewOverdue.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e) {
				resultsPanel.removeAll(); //might cause problems
				if(AdminInventorySearchPanel.this.DB.con != null) {
					Statement statement = null;
					ResultSet resultSet = null;
					try {
						statement = AdminInventorySearchPanel.this.DB.con.createStatement();
						resultSet = statement.executeQuery("SELECT DISTINCT InventoryID FROM inventory WHERE curdate() > DueDate AND CheckoutStatus = 'Checked Out';");

						while (resultSet.next()) {							
						
						    resultsPanel.add(new AdminItemSearchResult(resultSet.getInt("InventoryID"), AdminInventorySearchPanel.this.DB));	
						}
					} catch (SQLException eee) {
						//
					}
				}

				resultsPanel.revalidate();
				//resultsPanel.repaint();
			}
		});

		buttons.setLayout(new BoxLayout(buttons,BoxLayout.X_AXIS));
		buttons.add(addInventory);
		buttons.add(addInventoryButton);
		buttons.add(viewOverdue);

		//resultsScroll.add(resultsPanel);
		add(fieldSearch,BorderLayout.NORTH);
		//add(resultsScroll,BorderLayout.CENTER);
		add(resultsPanel);
		add(buttons,BorderLayout.SOUTH);
	}

}
